from flask import Flask, render_template, request, redirect, url_for, flash, session, jsonify
from werkzeug.security import check_password_hash, generate_password_hash  # 密码加密
import MySQLdb
import MySQLdb.cursors
import os
from datetime import datetime
import torch
# from apscheduler.schedulers.background import BackgroundScheduler

# main.py 中调用
# from model.inference import detect_anomaly
from sensorSave import monitor_and_insert
from collections import deque
import json

import time as tm
import threading

app = Flask(__name__)
app.secret_key = 'your_secret_key'  # 请使用一个强随机值
# scheduler = BackgroundScheduler()


file_path = os.path.join('D:/desktop/godluk', 'GAT_Transformer_5_24_1.json')

# 数据库配置
db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'SensorDB',
    'charset': 'utf8mb4'
}

previous_time = None
selected_sensors=[] #需要显示的传感器
bigged_selected_sensors = [] #需要放大的传感器

# 初始化全局存储（在Flask应用启动时执行）
loss_history = deque([0.0]*100, maxlen=100)
attack_history = deque([0]*100, maxlen=100)

window_place = 0
# monitor_and_insert('WADI_attackdataLABLE.csv', 'list.txt')

def get_db_connection():
    """建立并返回数据库连接"""
    try:
        connection = MySQLdb.connect(
            host=db_config['host'],
            user=db_config['user'],
            passwd=db_config['password'],
            db=db_config['database'],
            charset=db_config['charset'],
            cursorclass=MySQLdb.cursors.DictCursor  # 使用字典游标
        )
        return connection
    except MySQLdb.Error as e:
        print(f"MySQL Error: {e}")
        return None

@app.route('/')
def home():
    if 'username' in session:
        return render_template('home.html', username=session['username'])
    return redirect(url_for('login'))

@app.route('/login', methods=['GET', 'POST'])
#登录页面（显示）
def login():
    if request.method == 'POST':
        # 获取表单数据
        username = request.form.get('username')
        password = request.form.get('password')
        submit = request.form.get('submit')

        # 检查用户点击的是“登录”按钮
        if submit == '登录':
            if not username or not password:
                flash('请输入用户名和密码。', 'warning')
                return redirect(url_for('login'))

            connection = get_db_connection()
            if connection:
                try:
                    cursor = connection.cursor()  # 使用默认游标
                    # 查询用户
                    query = "SELECT UserPassword FROM Users WHERE UserName = %s"
                    cursor.execute(query, (username,))
                    user = cursor.fetchone()

                    if user:
                        # 验证密码
                        if check_password_hash(user['UserPassword'], password):
                            session['username'] = username
                            flash('登录成功！', 'success')
                            return redirect(url_for('sensors'))
                        else:
                            flash('密码错误，请重试。', 'danger')
                    else:
                        flash('用户名不存在，请注册。', 'danger')
                except MySQLdb.Error as e:
                    print(f"MySQL Error: {e}")
                    flash('数据库查询错误，请稍后再试。', 'danger')
                finally:
                    cursor.close()
                    connection.close()
            else:
                flash('数据库连接错误。', 'danger')

            return redirect(url_for('login'))
        elif submit == '注册':
            # 如果点击的是“注册”按钮，重定向到注册页面
            return redirect(url_for('register'))
        else:
            print(f"Invalid submit value: {submit}")
            flash('无效的提交。', 'warning')
            return redirect(url_for('login'))

    return render_template('login.html')

@app.route('/register', methods=['GET', 'POST'])
# 注册页面（显示）
def register():
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        confirm_password = request.form.get('confirm_password')
        submit = request.form.get('submit')

        if submit == "注册":
            if password != confirm_password:
                flash('密码和确认密码不一致，请重试。', 'danger')
                return redirect(url_for('register'))

            if not username:
                flash('用户名不能为空。', 'warning')
                return redirect(url_for('register'))

            connection = get_db_connection()
            if connection:
                try:
                    cursor = connection.cursor()  # 使用默认游标
                    # 检查用户名是否已存在
                    query = "SELECT UserName FROM Users WHERE UserName = %s"
                    cursor.execute(query, (username,))
                    user = cursor.fetchone()

                    if user:
                        flash('用户名已存在，请选择其他用户名。', 'danger')
                        return redirect(url_for('register'))

                    # 插入新用户
                    hashed_password = generate_password_hash(password)
                    insert_query = "INSERT INTO Users (UserName, UserPassword) VALUES (%s, %s)"
                    cursor.execute(insert_query, (username, hashed_password))
                    connection.commit()
                    flash('注册成功！请登录。', 'success')
                    return redirect(url_for('login'))
                except MySQLdb.Error as e:
                    print(f"MySQL Error: {e}")
                    flash('数据库错误，请稍后再试。', 'danger')
                finally:
                    cursor.close()
                    connection.close()
            else:
                flash('数据库连接错误。', 'danger')

            return redirect(url_for('register'))
        else:
            print(f"Invalid submit value: {submit}")

    return render_template('register.html')

@app.route('/sensors', methods=['GET','POST'])
#主页面：显示所有传感器（显示）
def sensors():
    # 获取传感器列表
    sensors = []
    if os.path.exists('list.txt'):
        with open('list.txt', 'r', encoding='utf-8') as f:
            sensors = [line.strip() for line in f if line.strip()]
    else:
        print("list.txt 文件不存在。")

    # 获取日志数据
    connection = get_db_connection()
    logs = []
    if connection:
        try:
            with connection.cursor() as cursor:
                query = "SELECT Time, State FROM Logs ORDER BY Time DESC"
                cursor.execute(query)
                logs = cursor.fetchall()
        except MySQLdb.Error as e:
            print(f"MySQL Error during fetching logs: {e}")
        finally:
            connection.close()

    # 获取选中的传感器（如果有）
    selected_sensor = request.args.get('sensor_name', '')
    print(selected_sensor)
    # 获取传感器数据（如果有）
    sensor_data = []
    if selected_sensor:
        try:
            connection = get_db_connection()
            if connection:
                with connection.cursor() as cursor:
                    query = """
                        SELECT Time, Data, State FROM Sensors
                        WHERE SensorName = %s
                        ORDER BY Time DESC
                    """
                    cursor.execute(query, (selected_sensor,))
                    sensor_data = cursor.fetchall()
        except MySQLdb.Error as e:
            print(f"MySQL Error during fetching sensor data: {e}")
        finally:
            if connection:
                connection.close()

    return render_template('sensors.html', logs=logs, sensors=sensors, selected_sensor=selected_sensor, sensor_data=sensor_data)

@app.route('/logout')
def logout():
    session.pop('username', None)
    flash('你已登出。', 'info')
    return redirect(url_for('login'))

@app.route('/get_latest_logs')
#获取日志数据（数据）
def get_latest_logs():
    """返回最新的日志数据"""
    logs = []
    connection = get_db_connection()
    if connection:
        try:
            with connection.cursor() as cursor:
                query = "SELECT Time, State FROM Logs ORDER BY Time DESC LIMIT 10"  # 获取最新10条日志
                cursor.execute(query)
                logs = cursor.fetchall()
                # 格式化日期为中文格式
                for log in logs:
                    log['Time'] = log['Time'].strftime('%Y年%m月%d日 %H:%M:%S')
        except MySQLdb.Error as e:
            print(f"MySQL Error during fetching logs: {e}")
        finally:
            connection.close()
    return jsonify(logs)

@app.route('/add_selected_sensors', methods=['GET','POST'])#通过下拉表选择要监视的传感器名称并存入selected_sensors
# 添加传感器（操作）
def add_selected_sensors():
    data = request.get_json()
    sensor = data.get('sensor')
    if sensor:
        selected_sensors.append(sensor)
        print("更新选中的传感器列表：", selected_sensors)
    return jsonify({"status": "success", "selected_sensors": selected_sensors})

@app.route('/delete_selected_sensors', methods=['GET','POST'])#通过下拉表选择要监视的传感器名称并存入selected_sensors
# 删除传感器（操作）
def delete_selected_sensors():
    data = request.get_json()
    sensor = data.get('sensor')
    if sensor:
        selected_sensors.remove(sensor)
        print("更新选中的传感器列表：", selected_sensors)
    return jsonify({"status": "success", "selected_sensors": selected_sensors})

@app.route('/big_selected_sensors', methods=['POST'])
# 添加需要放大显示的传感器（操作）
def big_selected_sensors():
    data = request.get_json()
    sensor = data.get('sensor')
    if sensor:
        bigged_selected_sensors.append(sensor)  # 使用集合避免重复
        print("更新放大的传感器列表：", bigged_selected_sensors)
    return jsonify({"status": "success"})

@app.route('/small_selected_sensors', methods=['POST'])
# 需要放大显示的传感器（操作）
def small_selected_sensors():
    data = request.get_json()
    sensor = data.get('sensor')
    if sensor in bigged_selected_sensors:
        bigged_selected_sensors.remove(sensor)
        print("更新放大的传感器列表：", bigged_selected_sensors)
    return jsonify({"status": "success"})

@app.route('/get_big_sensor_data', methods=['POST','GET'])
def get_big_sensor_data():
    """获取选中传感器的最新100条数据"""
    sensors_data = {}
    connection = get_db_connection()
    if connection:
        try:
            with connection.cursor() as cursor:
                for sensor in bigged_selected_sensors:
                    # 获取每个传感器的最新100条数据
                    query_data = """
                        SELECT Time, Data, State FROM Sensors
                        WHERE SensorName = %s
                        ORDER BY Time DESC
                        LIMIT 100
                    """
                    cursor.execute(query_data, (sensor,))
                    data = cursor.fetchall()
                    # 将数据按时间升序排列
                    data_sorted = sorted(data, key=lambda x: x['Time'])
                    for entry in data_sorted:
                        original_time = entry['Time']
                        # 提取秒数
                        formatted_time = original_time.second
                        entry['Time'] = formatted_time
                    sensors_data[sensor] = data_sorted
        except MySQLdb.Error as e:
            print(f"MySQL Error during fetching sensor data: {e}")
            flash('数据库错误，请稍后再试。', 'danger')
            return jsonify({"error": "数据库错误"}), 500
        finally:
            connection.close()
    else:
        flash('数据库连接错误。', 'danger')
        return jsonify({"error": "数据库连接错误"}), 500

    return jsonify(sensors_data)

@app.route('/get_sensor_data', methods=['GET','POST'])
# 获取传感器数据（数据）
def get_sensor_data():
    """获取选中传感器的最新100条数据"""
    sensors_data = {}
    connection = get_db_connection()
    if connection:
        try:
            with connection.cursor() as cursor:
                for sensor in selected_sensors:
                    # 获取每个传感器的最新100条数据
                    query_data = """
                        SELECT Time, Data, State FROM Sensors
                        WHERE SensorName = %s
                        ORDER BY Time DESC
                        LIMIT 100
                    """
                    cursor.execute(query_data, (sensor,))
                    data = cursor.fetchall()
                    # 将数据按时间升序排列
                    data_sorted = sorted(data, key=lambda x: x['Time'])
                    for entry in data_sorted:
                        original_time = entry['Time']
                        # 提取秒数
                        formatted_time = original_time.second
                        entry['Time'] = formatted_time

                    sensors_data[sensor] = data_sorted
                    


        except MySQLdb.Error as e:
            print(f"MySQL Error during fetching sensor data: {e}")
            flash('数据库错误，请稍后再试。', 'danger')
            return jsonify({"error": "数据库错误"}), 500
        finally:
            connection.close()
    else:
        flash('数据库连接错误。', 'danger')
        return jsonify({"error": "数据库连接错误"}), 500

    return jsonify(sensors_data)

    # scheduler.add_job(func=fetch_sensor_data, trigger="interval", seconds=60, coalesce=True)
    # scheduler.start()


@app.route('/attack', methods=['GET','POST'])
def attack():
    try:
        # 验证文件存在性
        if not os.path.exists(file_path):
            return jsonify({
                "status": "error",
                "message": "数据文件未找到",
                "error_code": 404
            }), 404

        # 读取并解析JSON
        with open(file_path, 'r') as f:
            data = json.load(f)

        # 数据有效性检查
        if 'all_losses' not in data:
            return jsonify({
                "status": "error",
                "message": "数据格式异常，缺少all_losses字段",
                "error_code": 500
            }), 500

        # 提取前100条并处理边界情况
        # losses_data = data['all_losses'][:100] if isinstance(data['all_losses'], list) else []

        global window_place
        losses_data = data['all_losses'][window_place % len(data['all_losses']):(window_place % len(data['all_losses']))+100]
        window_place += 1
        
        # 构造标准响应
        return jsonify({
            "status": "success",
            "data": losses_data
        })

    except json.JSONDecodeError:
        return jsonify({
            "status": "error",
            "message": "文件解析失败",
            "error_code": 500
        }), 500
    except Exception as e:
        return jsonify({
            "status": "error",
            "message": str(e),
            "error_code": 500
        }), 500
# 获取异常情况（数据）
# def attack_to_database():
# @app.route('/attack_to_database', methods=['GET','POST'])
# # 获取异常情况（数据）
# def attack_to_database():
#     # batch_x:torch.Size([1, 24, 79])
#     # batch_y:torch.Size([1, 1, 79])
#     """获取选中传感器的最新100条数据"""
#     sensors_data = {}
#     connection = get_db_connection()
#     if connection:
#         try:
#             with connection.cursor() as cursor:
#                 for sensor in selected_sensors:
#                     # 获取每个传感器的最新100条数据
#                     query_data = """
#                         SELECT Time, Data, State FROM Sensors
#                         WHERE SensorName = %s
#                         ORDER BY Time DESC
#                         LIMIT 25
#                     """
#                     cursor.execute(query_data, (sensor,))
#                     data = cursor.fetchall()
#                     # 将数据按时间升序排列
#                     data_sorted = sorted(data, key=lambda x: x['Time'])
#                     i = 1
#                     for entry in data_sorted:
#                         original_time = entry['Time']
#                         end_time = original_time
#                         # 提取秒数
#                         formatted_time = original_time.second
#                         entry['Time'] = formatted_time

#                     sensors_data[sensor] = data_sorted
#         except MySQLdb.Error as e:
#             print(f"MySQL Error during fetching sensor data: {e}")
#             flash('数据库错误，请稍后再试。', 'danger')
#             return jsonify({"error": "数据库错误"}), 500
#         # finally:
#         #     connection.close()
#     else:
#         flash('数据库连接错误。', 'danger')
#         return jsonify({"error": "数据库连接错误"}), 500
    
#     # 标准化处理流程
#     normalized_steps = []
#     for t in range(25):  # 遍历每个时间步
#         time_step_data = []
#         for sensor in selected_sensors:
#             # 提取该传感器所有时间点的数据值
#             sensor_values = [entry['Data'] for entry in sensors_data[sensor]]
            
#             # 计算该传感器的均值方差
#             mean = sum(sensor_values) / len(sensor_values)
#             std = (sum((x - mean)**2 for x in sensor_values)/len(sensor_values))**0.5
#             std = std if std != 0 else 1e-8
            
#             # 获取当前时间步标准化后的值
#             normalized_value = (sensor_values[t] - mean) / std
#             time_step_data.append(normalized_value)
        
#         normalized_steps.append(time_step_data)

#     # 构造张量格式
#     batch_x = torch.tensor([normalized_steps[:24]], dtype=torch.float32)  # 形状 [1,24,79]
#     batch_y = torch.tensor([normalized_steps[24:]], dtype=torch.float32)  # 形状 [1,1,79]

#     loss,attack = detect_anomaly(batch_x, batch_y)
#     # 更新历史记录（自动维护最新100条）
#     loss_history.append(float(loss.item()))  # 转换Tensor为float
#     attack_history.append(int(attack))       # 转为整型

#     # insert_sql = """
#     # INSERT INTO Sensors (SensorName, Time, Data, State)
#     # VALUES (%s, %s, %s, %s)
#     # ON DUPLICATE KEY UPDATE 
#     #     Data = VALUES(Data),
#     #     State = VALUES(State)
#     # """

#     # loss_value = float(loss.item()) if hasattr(loss, 'item') else float(loss)

#     # state = 'ALERT' if attack else 'NORMAL'
#     # if(end_time == previous_time):
#     #     return
#     # else:
#     #     cursor.execute(insert_sql, ('attack', end_time, loss_value, state))
#     #     previous_time = end_time
#     response = {
#         "sensor_data": sensors_data,
#         "analysis": {
#             "loss": list(loss_history),
#             "attack": list(attack_history),
#             "current_loss": float(loss.item()),
#             "current_attack": int(attack)
#         }
#     }
    
#     return jsonify(response)




if __name__ == '__main__':
    # 创建线程对象
    thread = threading.Thread(target=monitor_and_insert, args=('WADI_attackdataLABLE.csv', 'list.txt'))
    # 启动线程
    thread.start()
    tm.sleep(110)
    # atexit.register(lambda: scheduler.shutdown())
    app.run(debug=True)